package civicinity.ist.psu.edu.databases;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import civicinity.ist.psu.edu.constants.Constants;
import civicinity.ist.psu.edu.rssfeed.RSSDBItem;
import civicinity.ist.psu.edu.rssfeed.RSSItem;
import civicinity.ist.psu.edu.share.ShareItem;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.preference.PreferenceManager;
import android.util.Log;
import android.widget.Toast;

public class CivicinityDatabase extends SQLiteOpenHelper{

	private Context mContext;
	private SharedPreferences mPref;
	
	// constructor
	public CivicinityDatabase(Context context) {
		super(context, "civicinity_db.db", null, 1);
		mContext = context;
	}
	
	// constructor
	public CivicinityDatabase(Context context, String name, CursorFactory factory, int version) {
		super(context, name, factory, version);
		mPref = PreferenceManager.getDefaultSharedPreferences(context);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
		// create RSSFeeds table.
		db.execSQL("CREATE TABLE RSSFeeds (" +
				"id INTEGER PRIMARY KEY," +
				"rss_name TEXT," +
				"rss_url TEXT," +
				"rss_category INTEGER," + // 1:citizen 2:gov 3:org 4:media 5:biz 6:edu
				"rss_interval INTEGER" + // each rss has different frequency value, once it reaches 0, the app will download the feeds.
				");");
		
		// create RSSContents table.
		db.execSQL("CREATE TABLE RSSContents (" +
				"id INTEGER PRIMARY KEY AUTOINCREMENT," +
				"rss_id INTEGER," +
				"rss_title TEXT," +
				"rss_description TEXT," +
				"rss_link TEXT," +
				"rss_category TEXT," +
				"rss_pubdate TEXT" +
				");");
		
		// create RSSContents table for background search
		db.execSQL("CREATE TABLE RSSContentsTemp (" +
				"id INTEGER PRIMARY KEY AUTOINCREMENT," +
				"rss_id INTEGER," +
				"rss_title TEXT," +
				"rss_description TEXT," +
				"rss_link TEXT," +
				"rss_category TEXT," +
				"rss_pubdate TEXT" +
				");");
		
		// create ShareMe table for background search
		db.execSQL("CREATE TABLE ShareMe (" +
				"id INTEGER PRIMARY KEY AUTOINCREMENT," +
				"local_photo_url TEXT," +
				"category TEXT," +
				"description TEXT," +
				"time NUMERIC," +
				"latitude NUMERIC," +
				"longitude NUMERIC," +
				"accuracy NUMERIC," +
				"uploaded NUMERIC DEFAULT 0" +
				");");		
		
		// create ShareMe table for background search
		db.execSQL("CREATE TABLE ShareOthers (" +
				"id INTEGER PRIMARY KEY," +
				"category TEXT," +
				"description TEXT," +
				"username TEXT," +
				"time NUMERIC," +
				"latitude NUMERIC," +
				"longitude NUMERIC," +
				"accuracy NUMERIC," +
				"image_url TEXT," +
				"comment_cnt NUMERIC," +
				"like NUMERIC DEFAULT 0," + // like the post? (Yes : 1, No : 0)
				"checkin_cnt NUMERIC DEFAULT 0," +
				"distance NUMERIC DEFAULT 0" +
				");");	
		
		db.execSQL("CREATE TABLE MyComment (" + 
				"share_id INTEGER, " +
				"contents TEXT, " +
				"date INTEGER" +
				");");
		
		// Insert data into RSSFeeds table
		// update in every 1 hour and show the msg.
		// Max interval : 12 hours.
		// Min interval : 1 hour.
		//db.execSQL("INSERT INTO RSSFeeds VALUES(1, \"CentreDaily SC\", \"http://www.centredaily.com/statecollege/index.rss\", 4)"); <- they are using non-standard format.
		//db.execSQL("INSERT INTO RSSFeeds VALUES(1, \"CentreDaily\", \"http://www.centredaily.com/news/local/index.rss\", 4)"
		
		db.execSQL("INSERT INTO RSSFeeds VALUES(1, \"Collegian\", \"http://www.collegian.psu.edu/all.rss\", 4, 1)");		
		db.execSQL("INSERT INTO RSSFeeds VALUES(2, \"Statecollege.com\", \"http://www.statecollege.com/news/rss.php\", 4, 1)");
		db.execSQL("INSERT INTO RSSFeeds VALUES(3, \"CentreDaily\", \"http://www.centredaily.com/pennstate/index.rss\", 4, 6)");
		db.execSQL("INSERT INTO RSSFeeds VALUES(4, \"Onward State\", \"http://feeds.feedburner.com/OnwardState?format=xml\",4, 24)");
		db.execSQL("INSERT INTO RSSFeeds VALUES(5, \"Penn State Live\", \"http://live.psu.edu/rss/30\", 6, 2)");
		db.execSQL("INSERT INTO RSSFeeds VALUES(6, \"Centre Country Government\", \"http://www.co.centre.pa.us/whatsnew.xml\", 2, 8)");
		db.execSQL("INSERT INTO RSSFeeds VALUES(7, \"State College Spikes News\", \"http://web.minorleaguebaseball.com/partnerxml/gen/news/rss/t1174.xml\", 4, 3)");
		db.execSQL("INSERT INTO RSSFeeds VALUES(8, \"Schlow Memorial Library\", \"http://schlowlibrary.org/rss.xml\", 3, 24)");
		db.execSQL("INSERT INTO RSSFeeds VALUES(9, \"PSU Parks & Recreation\", \"https://www.facebook.com/feeds/page.php?format=rss20&id=147397014295\", 3, 8)");
		
		//db.execSQL("INSERT INTO RSSFeeds VALUES(4, \"StateCollege.com\", \"http://twitter.com/statuses/user_timeline/25525589.rss\", 4)");		
		//db.execSQL("INSERT INTO RSSFeeds VALUES(8, \"Line Backer Mike\", \"http://feeds.feedburner.com/LBU\", 1)");
		//db.execSQL("INSERT INTO RSSFeeds VALUES(9, \"WPSU\", \"https://www.facebook.com/feeds/page.php?format=atom10&id=78040148489\", 3)");
		//db.execSQL("INSERT INTO RSSFeeds VALUES(10, \"Nittany Valley Symphony\", \"http://www.nvs.org/index.php?format=feed&type=rss\", 3)");
		
		// sometimes wrong format
		//db.execSQL("INSERT INTO RSSFeeds VALUES(8, \"Central Pennsylvania Convention and Visitors Bureau\", \"http://www.facebook.com/feeds/page.php?format=rss20&id=146908293416\", 3)");
		
		// Not used nor updated
		//db.execSQL("INSERT INTO RSSFeeds VALUES(8, \"Penn State Science\", \"http://science.psu.edu/frontpagenews/RSS\", 6)");
		//db.execSQL("INSERT INTO RSSFeeds VALUES(9, \"Penn State Students Recycle\", \"https://www.facebook.com/feeds/page.php?format=rss20&id=114948638560178\", 2)");
		//db.execSQL("INSERT INTO RSSFeeds VALUES(8, \"State College Accuweather\", \"http://rss.accuweather.com/rss/liveweather_rss.asp?metric=0&locCode=16804\", 4)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS RSSFeeds;");
		db.execSQL("DROP TABLE IF EXISTS RSSContents;");
		db.execSQL("DROP TABLE IF EXISTS RSSContentsTemp;");
		db.execSQL("DROP TABLE IF EXISTS ShareMe;");
		db.execSQL("DROP TABLE IF EXISTS ShareOthers;");
		db.execSQL("DROP TABLE IF EXISTS MyComments;");
		
		onCreate(db);
	}
	
	public void setMyComment(int shareID, long date, String contents) {
		
		/**
		 * db.execSQL("CREATE TABLE MyComment (" + 
				"share_id INTEGER, " +
				"contents TEXT, " +
				"date TEXT" +
				");");
		 */
		
		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase wDB = db.getWritableDatabase();
		
		wDB.execSQL("INSERT INTO MyComment VALUES(" + 
				shareID + "," +
				"\"" + contents + "\"," +
				 + date +
				");" 
				);
		
		wDB.close();
		
		Log.i("K", "comment is added in shareID=" + shareID);
		
	}
	
	public void setShareOthers(ArrayList<ShareItem> arr) {

		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase wDB = db.getWritableDatabase();
		
		wDB.execSQL("DELETE FROM ShareOthers");
		
		for(int i = 0 ; i < arr.size(); i++) {
			wDB.execSQL("INSERT INTO ShareOthers VALUES(" +
					arr.get(i).getEventID() + "," +
					"\"" + arr.get(i).getEventCategory() + "\"," +
					"\"" + arr.get(i).getEventDescription() + "\"," +
					"\"" + arr.get(i).getEventUsername() + "\"," +
					arr.get(i).getEventDate() + "," +
					arr.get(i).getEventLatitude() + "," +
					arr.get(i).getEventLongitude() + "," +
					arr.get(i).getEventAccuracy() + "," +
					"\"" + arr.get(i).getEventPhotoUrl() + "\"," +
					arr.get(i).getEventCommentCnt() + "," +
					arr.get(i).getEventLike() + "," +
					arr.get(i).getEventCheckInCnt() + "," +
					arr.get(i).getEventDistance() +
					");"
					);
		}
	
		
		Log.i("K", "insert data into ShareOthers");
		
		wDB.close();
	}
	
	
	public void updateShareCheckIn(int shareID, int checkInCnt) {
		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase wDB = db.getWritableDatabase();
		
		wDB.execSQL("UPDATE ShareOthers SET checkin_cnt=" + (checkInCnt+1) + " WHERE id=" + shareID);
		
		wDB.close();
	
		Log.i("K", "update CheckIn value in the table.");
	}
	
	public void updateShareLike(int shareID) {
		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase wDB = db.getWritableDatabase();
		
		wDB.execSQL("UPDATE ShareOthers SET like=1 WHERE id=" + shareID);
		
		wDB.close();
	
		Log.i("K", "update LIKE value in the table.");
	}
	
	public void updateShareOthersComment(int shareID, int commentCnt) {
		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase wDB = db.getWritableDatabase();
		
		wDB.execSQL("UPDATE ShareOthers SET comment_cnt=" + commentCnt + " WHERE id=" + shareID);
		
		wDB.close();
		
		Log.i("K", "Increment the num of comment in the table");
	}
	
	
	public ShareItem getSingleShareOthers(int id) {
		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase rDB = db.getReadableDatabase();
		
		ShareItem item = new ShareItem();
		
		Cursor cursor = rDB.rawQuery("SELECT id, category, description, username, time, latitude, longitude, accuracy, image_url, comment_cnt, like, checkin_cnt " +
				"FROM ShareOthers " +
				"WHERE id=" + id, null);
		
		while(cursor.moveToNext()) {
			item.setEventID(cursor.getInt(0));
			item.setEventCategory(cursor.getString(1));
			item.setEventDescription(cursor.getString(2));
			item.setEventUsername(cursor.getString(3));
			item.setEventDate(cursor.getString(4));
			item.setEventLatitude(cursor.getDouble(5));
			item.setEventLongitude(cursor.getDouble(6));
			item.setEventAccuracy(cursor.getFloat(7));
			item.setEventPhotoUrl(cursor.getString(8));
			item.setEventCommentCnt(cursor.getInt(9));
			item.setEventLike(cursor.getInt(10));
			item.setEventCheckInCnt(cursor.getInt(11));
		}
		
		cursor.close();
		rDB.close();
		
		return item;
	}
	
	// type = 0 : 
	// type = 1 : 
	public ArrayList<ShareItem> getShareOthers(int type) {
		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase rDB = db.getReadableDatabase();
		
		ArrayList<ShareItem> arr = new ArrayList<ShareItem>();
		
		/**
		 * // create ShareMe table for background search
		db.execSQL("CREATE TABLE ShareOthers (" +
				"id INTEGER PRIMARY KEY," +
				"category TEXT," +
				"description TEXT," +
				"time NUMERIC," +
				"latitude NUMERIC," +
				"longitude NUMERIC," +
				"accuracy NUMERIC," +
				");");	
		
		 */
		
		Cursor cursor = null;
		
		// type 0 is all
		if(type == 0) {
			cursor = rDB.rawQuery("SELECT id, category, description, username, time, latitude, longitude, accuracy, image_url, comment_cnt, like, checkin_cnt, distance " +
					"FROM ShareOthers " +
					"ORDER BY time DESC", null);
		}
		// type 1 is only by user_id
		else if(type == 1) {
			SharedPreferences mPref = PreferenceManager.getDefaultSharedPreferences(mContext);
			
			cursor = rDB.rawQuery("SELECT id, category, description, username, time, latitude, longitude, accuracy, image_url, comment_cnt, like, checkin_cnt, distance " +
					"FROM ShareOthers " +
					"WHERE username=\"" + mPref.getString("username", "") + "\"" + 
					"ORDER BY time DESC", null);
		}
		
		
		while(cursor.moveToNext()) {
			ShareItem item = new ShareItem();
			item.setEventID(cursor.getInt(0));
			item.setEventCategory(cursor.getString(1));
			item.setEventDescription(cursor.getString(2));
			item.setEventUsername(cursor.getString(3));
			item.setEventDate(cursor.getString(4));
			item.setEventLatitude(cursor.getDouble(5));
			item.setEventLongitude(cursor.getDouble(6));
			item.setEventAccuracy(cursor.getFloat(7));
			item.setEventPhotoUrl(cursor.getString(8));
			item.setEventCommentCnt(cursor.getInt(9));
			item.setEventLike(cursor.getInt(10));
			item.setEventCheckInCnt(cursor.getInt(11));
			item.setEventDistance(cursor.getInt(12));
			
			arr.add(item);
			
		}
		
		cursor.close();
		rDB.close();
		
		return arr;
		
		
	}
	
	public void updateShareMe(int id) {
		
		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase wDB = db.getWritableDatabase();

		wDB.rawQuery("UPDATE ShareMe SET uploaded=1 WHERE id=" + id, null);
		
		
		Log.i("K", "Updated successfully. share_id : " + id);
		
		wDB.close();
	}
	
	public int setShareMe(String photoUrl, String category, String description, long time, double latitude, double longitude, float accuracy) {

		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase wDB = db.getWritableDatabase();
		
		Log.i("K", "INSERT INTO ShareMe VALUES(" +
				"null," +
				"\"" + photoUrl + "\"," +
				"\"" + category + "\"," +
				"\"" + description + "\"," +
				time + "," +
				latitude + "," +
				longitude + "," +
				accuracy + "," +
				0 + // uploaded? false
				");");
		
		wDB.execSQL("INSERT INTO ShareMe VALUES(" +
				"null," +
				"\"" + photoUrl + "\"," +
				"\"" + category + "\"," +
				"\"" + description + "\"," +
				time + "," +
				latitude + "," +
				longitude + "," +
				accuracy + "," +
				0 + 
				");"
				);
		
		Log.i("K", "update CDT news complete");
		
		wDB.close();
		
		SQLiteDatabase rDB = db.getReadableDatabase();
		Cursor cursor = rDB.rawQuery("SELECT MAX(id) FROM ShareMe", null);
		cursor.moveToNext();
		
		int idValue = cursor.getInt(0);
		
		
		cursor.close();
		rDB.close();
		
		
		return idValue;
	}
	
	public ShareItem getSingleShareMe(int id) {
		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase rDB = db.getReadableDatabase();
		
		ShareItem item = new ShareItem();
		
		Cursor cursor = rDB.rawQuery("SELECT id, local_photo_url, category, description, time, latitude, longitude, accuracy, uploaded " +
				"FROM ShareMe WHERE id=" + id, null);
		
		while(cursor.moveToNext()) {
			item.setEventID(cursor.getInt(0));
			item.setEventPhotoUrl(cursor.getString(1));
			item.setEventCategory(cursor.getString(2));
			item.setEventDescription(cursor.getString(3));
			item.setEventDate(cursor.getString(4));
			item.setEventLatitude(cursor.getDouble(5));
			item.setEventLongitude(cursor.getDouble(6));
			item.setEventAccuracy(cursor.getFloat(7));
		}
		
		cursor.close();
		rDB.close();
		
		return item;
	}
	
	public ArrayList<ShareItem> getShareMe() {
		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase rDB = db.getReadableDatabase();
		
		ArrayList<ShareItem> arr = new ArrayList<ShareItem>();
		
		Cursor cursor = rDB.rawQuery("SELECT id, local_photo_url, category, description, time, latitude, longitude, accuracy, uploaded " +
				"FROM ShareMe ORDER BY time DESC", null);
		
		while(cursor.moveToNext()) {
			ShareItem item = new ShareItem();
			item.setEventID(cursor.getInt(0));
			item.setEventPhotoUrl(cursor.getString(1));
			item.setEventCategory(cursor.getString(2));
			item.setEventDescription(cursor.getString(3));
			item.setEventDate(cursor.getString(4));
			item.setEventLatitude(cursor.getDouble(5));
			item.setEventLongitude(cursor.getDouble(6));
			item.setEventAccuracy(cursor.getFloat(7));
			// not adds uploaded yet..
			
			arr.add(item);
		}
		
		cursor.close();
		rDB.close();
		
		return arr;
	}
	
	public RSSDBItem getRSSInfo(int rssID) {
		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase rDB = db.getReadableDatabase();
		
		RSSDBItem item = new RSSDBItem();
		
		Cursor cursor = rDB.rawQuery("SELECT rss_name, rss_url FROM RSSFeeds WHERE id=" + rssID, null);
		while(cursor.moveToNext()) {
			item.setRSSName(cursor.getString(0));
			item.setRSSUrl(cursor.getString(1));
		}
		
		cursor.close();
		rDB.close();
		
		return item;
		
	}
	
	public void updateRSSContents(ArrayList<RSSItem> arr, int rssID) {
		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase wDB = db.getWritableDatabase();
		
		wDB.execSQL("DELETE FROM RSSContents WHERE rss_id=" + rssID);
		
		Log.i("K", "Message : " + "DELETE FROM RSSContents WHERE rss_id=" + rssID);
		
		for(int i = 0 ; i < arr.size() ; i++) {
			// ignore wrongly formatted value
			if(arr.get(i).getPubDate().equals("bad value")) {
				continue;
			}
			wDB.execSQL("INSERT INTO RSSContents VALUES(" +
					"null," +
					rssID + "," +
					"\"" + arr.get(i).getTitle().replaceAll("\"", "\'") + "\"," +
					"\"" + arr.get(i).getDescription().replaceAll("\"", "\'") + "\"," +
					"\"" + arr.get(i).getLink() + "\"," +
					"\"" + arr.get(i).getCategory() + "\"," +
					"\"" + arr.get(i).getPubDate() + "\"" + 
					");"
					);
		}
		
		Log.i("K", "update CDT news complete");
		
		wDB.close();
	}
	
	public void setRSSContents(ArrayList<RSSItem> arr, int version) {
		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase wDB = db.getWritableDatabase();
		
		if(version == Constants.MAIN_RSSContents) {
			wDB.execSQL("DELETE FROM RSSContents");

			Log.i("K", "Insert into main RSSContents");
			
			for(int i = 0 ; i < arr.size() ; i++) {
				// ignore wrongly formatted value
				if(arr.get(i).getPubDate().equals("bad value")) {
					continue;
				}
				wDB.execSQL("INSERT INTO RSSContents VALUES(" +
						"null," +
						arr.get(i).getRSSID() + "," +
						"\"" + arr.get(i).getTitle().replaceAll("\"", "\'") + "\"," +
						"\"" + arr.get(i).getDescription().replaceAll("\"", "\'") + "\"," +
						"\"" + arr.get(i).getLink() + "\"," +
						"\"" + arr.get(i).getCategory() + "\"," +
						"\"" + arr.get(i).getPubDate() + "\"" + 
						");"
						);
			}
		}
		else if(version == Constants.TEMP_RSSContents){
			
			// open the editor
    		//SharedPreferences.Editor editor = mPref.edit();
    		//editor.putBoolean("data_transfer", true);
    		//editor.commit();
			
			Log.i("K", "DELETE FROM RSSContentsTemp WHERE rss_id=" + arr.get(0).getRSSID());
			wDB.execSQL("DELETE FROM RSSContentsTemp WHERE rss_id=" + arr.get(0).getRSSID());
			
			Log.i("K", "Insert into temp RSSContents");
			
			for(int i = 0 ; i < arr.size() ; i++) {
				// ignore wrongly formatted value
				if(arr.get(i).getPubDate().equals("bad value")) {
					continue;
				}
				wDB.execSQL("INSERT INTO RSSContentsTemp VALUES(" +
						"null," +
						arr.get(i).getRSSID() + "," +
						"\"" + arr.get(i).getTitle().replaceAll("\"", "\'") + "\"," +
						"\"" + arr.get(i).getDescription().replaceAll("\"", "\'") + "\"," +
						"\"" + arr.get(i).getLink() + "\"," +
						"\"" + arr.get(i).getCategory() + "\"," +
						"\"" + arr.get(i).getPubDate() + "\"" + 
						");"
						);
			}

			Log.i("K", "Transfer the data from RSSContentsTemp to RSSContents");
			// Transfer values from RSSContentsTemp to RSSContents
			
			Log.i("K", "DELETE FROM RSSContents WHERE rss_id=" + arr.get(0).getRSSID());
			wDB.execSQL("DELETE FROM RSSContents WHERE rss_id=" + arr.get(0).getRSSID());
			
			Cursor cursor = wDB.rawQuery("SELECT id, rss_id, rss_title, rss_description, rss_link, rss_category, rss_pubdate " +
					"FROM RSSContentsTemp WHERE rss_id=" + arr.get(0).getRSSID(), null);
			
			while(cursor.moveToNext()) {
				wDB.execSQL("INSERT INTO RSSContents VALUES(" +
						"null," +
						cursor.getInt(1) + "," +
						"\"" + cursor.getString(2) + "\"," +
						"\"" + cursor.getString(3).replaceAll("\"", "\'") + "\"," +
						"\"" + cursor.getString(4) + "\"," +
						"\"" + cursor.getString(5) + "\"," +
						"\"" + cursor.getString(6) + "\"" + 
						");"
						);
			}
			
			//editor.putBoolean("data_transfer", false);
    		//editor.commit();
			
			Log.i("K", "Data is in RSSContentTemp");
			
			cursor.close();
		}
		
		wDB.close();
	}
	
	/**
	 * the parameter is to indicate which category is requested.
	 * 1 - breaking news
	 * 2 - community events
	 * 3 - penn state news
	 * @param threeCategory
	 * @return
	 */
	public ArrayList<RSSItem> getRSSAllContents(int threeCategory) {
		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase rDB = db.getReadableDatabase();
		
		String queryStr = "";
		
		// breaking news
		if(threeCategory == 0) {
			queryStr = "SELECT " +
			"RSSF.rss_category, " +
			"RSSF.rss_url, " +
			"RSSC.id, " +
			"RSSC.rss_id, " +
			"RSSC.rss_title, " +
			"RSSC.rss_description, " +
			"RSSC.rss_link, " +
			"RSSC.rss_category, " +
			"RSSC.rss_pubdate " +
			"FROM RSSContents AS RSSC " +
			"JOIN RSSFeeds AS RSSF ON RSSF.id=RSSC.rss_id " +
			"WHERE RSSC.rss_id=" + 1 + " " +
			"ORDER BY RSSC.rss_pubdate DESC " + 
			"LIMIT 20";
		}
		// community news //1:citizen 2:gov 3:org 4:media 5:biz
		else if(threeCategory == 1) {
			queryStr = "SELECT " +
			"RSSF.rss_category, " +
			"RSSF.rss_url, " +
			"RSSC.id, " +
			"RSSC.rss_id, " +
			"RSSC.rss_title, " +
			"RSSC.rss_description, " +
			"RSSC.rss_link, " +
			"RSSC.rss_category, " +
			"RSSC.rss_pubdate " +
			"FROM RSSContents AS RSSC " +
			"JOIN RSSFeeds AS RSSF ON RSSF.id=RSSC.rss_id " +
			"ORDER BY RSSC.rss_pubdate DESC"; 
			//"WHERE RSSF.rss_category=" + 1 + " OR " + 2 + " OR " + 3 + " OR " + 4 + " OR " + 5;
		}
		// psu news // 6:edu
		else {
			queryStr = "SELECT " +
			"RSSF.rss_category, " +
			"RSSF.rss_url, " +
			"RSSC.id, " +
			"RSSC.rss_id, " +
			"RSSC.rss_title, " +
			"RSSC.rss_description, " +
			"RSSC.rss_link, " +
			"RSSC.rss_category, " +
			"RSSC.rss_pubdate " +
			"FROM RSSContents AS RSSC " +
			"JOIN RSSFeeds AS RSSF ON RSSF.id=RSSC.rss_id " +
			"WHERE RSSF.rss_category=" + 6 + " " +
			"ORDER BY RSSC.rss_pubdate DESC " +
			"LIMIT 20";
		}

		Cursor cursor = rDB.rawQuery(queryStr, null);

		ArrayList<RSSItem> arr = new ArrayList<RSSItem>();
		
		Log.i("K", "cursor count : " + cursor.getCount());
		
		while(cursor.moveToNext()) {

			RSSItem item = new RSSItem();
			item.setRSSCategory(cursor.getInt(0));
			item.setRSSURL(cursor.getString(1));
			item.setID(cursor.getInt(2));
			item.setRSSID(cursor.getInt(3));
			item.setTitle(cursor.getString(4));
			item.setDescription(cursor.getString(5));
			item.setLink(cursor.getString(6));
			item.setCategory(cursor.getString(7));
			item.setPubDate(cursor.getString(8));
			
			arr.add(item);
		}
		
		cursor.close();
		rDB.close();
		
		// special care for category number : 1
		if(threeCategory == 1) {
			
			Log.i("K", "count : " + arr.size());
			
			ArrayList<RSSItem> rssArrTemp = new ArrayList<RSSItem>();
			int limitCnt = 0;
			
			for(int i = 0 ; i < arr.size() ; i++) {
				if(limitCnt == 20) {
					break;
				}
				if(arr.get(i).getRSSID() != 1 && (arr.get(i).getRSSCategory() == 1 || 
						arr.get(i).getRSSCategory() == 2 ||
						arr.get(i).getRSSCategory() == 3 ||
						arr.get(i).getRSSCategory() == 4 ||
						arr.get(i).getRSSCategory() == 5)) {
					rssArrTemp.add(arr.get(i));
					limitCnt++;
				}
			}
			
			return rssArrTemp;
		}
		
		
		
		return arr;
	}
	
	public ArrayList<RSSItem> getRSSContents(int rssID) {
		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase rDB = db.getReadableDatabase();
		
		Cursor cursor = rDB.rawQuery("SELECT " +
				"RSSF.rss_category, " +
				"RSSF.rss_url, " +
				"RSSC.id, " +
				"RSSC.rss_id, " +
				"RSSC.rss_title, " +
				"RSSC.rss_description, " +
				"RSSC.rss_link, " +
				"RSSC.rss_category, " +
				"RSSC.rss_pubdate " +
				"FROM RSSContents AS RSSC " +
				"JOIN RSSFeeds AS RSSF ON RSSF.id=RSSC.rss_id " +
				"WHERE RSSC.rss_id=" + rssID + " " +
				"ORDER BY RSSC.rss_pubdate DESC " +
				"LIMIT 20", null);
		
		ArrayList<RSSItem> arr = new ArrayList<RSSItem>();
		
		Log.i("K", "cursor count : " + cursor.getCount());
		
		while(cursor.moveToNext()) {

			RSSItem item = new RSSItem();
			item.setRSSCategory(cursor.getInt(0));
			item.setRSSURL(cursor.getString(1));
			item.setID(cursor.getInt(2));
			item.setRSSID(cursor.getInt(3));
			item.setTitle(cursor.getString(4));
			item.setDescription(cursor.getString(5));
			item.setLink(cursor.getString(6));
			item.setCategory(cursor.getString(7));
			item.setPubDate(cursor.getString(8));
			
			arr.add(item);
		}
		
		cursor.close();
		rDB.close();
		
		return arr;
	}
	
	public ArrayList<RSSDBItem> getFeeds() {
	
		CivicinityDatabase db = new CivicinityDatabase(mContext);
		SQLiteDatabase rDB = db.getReadableDatabase();
		
		Cursor cursor = rDB.rawQuery("SELECT id, rss_name, rss_url, rss_interval FROM RSSFeeds", null);
		
		ArrayList<RSSDBItem> array = new ArrayList<RSSDBItem>();
		
		while(cursor.moveToNext()) {
	
			RSSDBItem item = new RSSDBItem();
			item.setID(cursor.getInt(0));
			item.setRSSName(cursor.getString(1));
			item.setRSSUrl(cursor.getString(2));
			item.setRSSInterval(cursor.getInt(3));
		
			array.add(item);
		}
		
		cursor.close();
		rDB.close();
		
		return array;
	}
}
